package com.yan.jdbc;

import org.junit.jupiter.api.Test;

import java.sql.*;
import java.util.LinkedList;

/**
 * @author taoge
 * @Date 2024/8/22 11:15
 * @Description
 */
public class JdbcDemo1 {
    public static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/jdbc_db1?useUnion=true&useSSL=false&characterEncoding=UTF8";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "123456";

    @Test // 添加一条记录
    public void test() throws Exception {
        // 加载数据库驱动
        Class.forName(MYSQL_DRIVER);
        // 获取数据库连接
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        // 获取发送sql的对象
        Statement statement = connection.createStatement();
        // 定义sql语句
        String sql = "insert into student value(null , \"jack\",18,\"男\",\"武汉\")";
        // 使用statement发送SQL语句 , 返回受影响行数
        int count = statement.executeUpdate(sql);
        System.out.println(count == 1 ? "添加成功" : "添加失败");
        // 关闭资源
        statement.close();
        connection.close();
    }

    @Test // 修改一条数据
    public void test1() throws Exception {
        // 加载数据库驱动
        Class.forName(MYSQL_DRIVER);
        // 获取连接
        Connection connection = DriverManager.getConnection(URL , USERNAME ,PASSWORD);
        // 获取传输对象
        Statement statement = connection.createStatement();
        // sql语句
        String sql = "update student set name = 'tom' where id = 2";
        // 传输sql语句
        int count = statement.executeUpdate(sql);
        System.out.println(count == 1 ? "修改成功":"修改失败");
        // 关资源
        statement.close();
        connection.close();
    }

    @Test // 删除一条记录
    public void test2(){
        // 加载数据库驱动
        Connection connection = null;
        Statement statement = null;
        try {
            Class.forName(MYSQL_DRIVER);
            // 获取连接
            connection = DriverManager.getConnection(URL , USERNAME ,PASSWORD);
            // 获取传输对象
            statement = connection.createStatement();
            // sql语句
            String sql = "delete from student  where id = 2";
            // 传输sql语句
            int count = statement.executeUpdate(sql);
            System.out.println(count == 1 ? "修改成功":"修改失败");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            // 关资源
            try {
                statement.close();
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Test // 查询记录
    public void test3() throws Exception {
        // 加载数据库驱动
        Class.forName(MYSQL_DRIVER);
        // 获取数据库连接
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        // 获取发送sql的对象
        Statement statement = connection.createStatement();
        // 定义sql语句
        String sql = "select * from student";
        // 发送
        LinkedList<Student> list = new LinkedList<>();
        ResultSet rs = statement.executeQuery(sql);
        while(rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            int age = rs.getInt(3);
            String sex = rs.getString("sex");
            String address = rs.getString("address");

            Student student = new Student(id, name, age, sex, address);
            list.add(student);
        }

        list.forEach(item-> System.out.println(item));

        rs.close();
        statement.close();
        connection.close();
    }
}
